USE [NANS21V]
GO

/****** オブジェクト: SqlProcedure [dbo].[SyozokuSyukei] スクリプト日付: 2014/05/14 17:26:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SyozokuSyukei]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SyozokuSyukei];
GO

CREATE PROCEDURE [dbo].[SyozokuSyukei]
	@TAIKAINO int,
	@TAIKOSENNO int
AS
	SELECT 
		A.TAIKOSENNO,
		A.HOUHOU,
		C.SYOZOKUNO,
		C.SYOZOKUMEI,
		D.SYOZOKUTIMEI,
		B.SYUKEINO,
		B.JYUNI,
		B.TOKUTEN,
		B.BONUS,
		B.SANKA 
	FROM 
		TBL_TAIKOU AS A 
	LEFT JOIN 
		TBL_TAIKOU_KEKKA AS B 
	ON 
		A.TAIKAINO = B.TAIKAINO 
		AND A.TAIKOSENNO = B.TAIKOSENNO 
	LEFT JOIN 
		TBL_SYOZOKU AS C 
	ON 
		A.TAIKAINO = C.TAIKAINO 
		AND B.SYOZOKU = C.SYOZOKUNO 
	LEFT JOIN 
		TBL_SYOZOKUTI AS D 
	ON 
		C.SYOZOKUTINO = D.SYOZOKUTINO 
	WHERE 
		A.TAIKAINO = @TAIKAINO 
		AND A.TAIKOSENNO = @TAIKOSENNO 
	ORDER BY 
		B.JYUNI,
		B.TOKUTEN DESC,
		C.SYOZOKUNO